#!/usr/bin/python3

import pandas as pd
import json
import copy


def excel_column_to_index(column_str):
    """
    将Excel列索引字符串转换为整数索引。

    :param column_str: Excel列索引字符串，例如 'A', 'B', 'Z', 'AA', 'AB' 等
    :return: 对应的整数索引
    """
    index = 0
    for char in column_str:
        index = index * 26 + (ord(char) - ord('A') + 1)
    return index - 1


class ExcelInfo:
    path = ""
    filename = ""
    exclude_sheets = ""
    title_row = 0
    maps_rule = None
    sort_by = ""
    reference = ""

    # excel文件数据
    xls: pd.ExcelFile = None

    # 记录原始表格中的数据行数量, 方便重新整理数据时快速截取
    origin_lens = {}

    # 存储所有按sheet分类的信息
    sheet_info = {}

    def __init__(self, path: str, excel_info):
        self.path = path
        self.filename = excel_info['name']
        self.exclude_sheets = excel_info['exclude_sheets']
        self.title_row = excel_info['title_row']
        self.maps_rule = excel_info['maps_rule']
        self.sort_by = excel_info['sort_by']
        self.reference = excel_info['reference']
        # 载入excel
        self.load_excel()
        # 解析sheet
        if self.parse_sheets() is False:
            return
        # 整理数据
        self.resort_sheet_data()
        pass

    def load_excel(self):
        print("正在载入 ", self.filename)
        self.xls = pd.ExcelFile(self.path + self.filename)
        print("载入完成，所有的sheet页: ", self.xls.sheet_names)
        pass

    def parse_sheets(self) -> bool:
        if self.xls is None:
            print("No xls is load")
            return False

        print("开始解析所有sheets")
        for sheet in self.xls.sheet_names:
            if sheet not in self.exclude_sheets:
                self.parse_sheet(sheet)
                #####
                # return True
        return True

    def parse_sheet(self, sheet_name: str):
        print("正在解析sheet页: " + sheet_name)
        # 使用列表来存储sheet页中的行数据对象
        self.sheet_info[sheet_name] = []
        sheet_data = pd.read_excel(self.xls, sheet_name=sheet_name, skiprows=self.title_row - 1)

        lens = len(sheet_data)
        self.origin_lens[sheet_name] = lens
        print("扫描到的数据行数(可能包含空行或者其它无效子表数据): ", lens)

        for index, row in sheet_data.iterrows():
            if pd.isnull(row[self.reference]):
                print("在", self.title_row + index + 1, "行处发现无效行, 正在截取数据表")
                sheet_data = sheet_data.iloc[0:index, :]
                lens = len(sheet_data)
                self.origin_lens[sheet_name] = lens
                print("修正后的数据行数: ", lens)
                break
            pass

        to_key_name = []
        target_data = {}

        # 抓取maps规则中的from obj声明的列
        for maps in self.maps_rule:
            to = maps['to']
            to_key_name.append(maps['to'])
            if 'title' in maps['from']:
                key = maps['from']['title']  # 截取指定title名的列
                data = sheet_data[key]
                target_data[to] = data.values  # []中元素可能存在nan
                pass
            else:
                col = maps['from']['col']
                data = sheet_data.iloc[0:, excel_column_to_index(col)]  # 截取指定列号的列
                target_data[to] = data.values
                pass
            pass

        # 数据转置 重新解析为'行数据对象'
        for i in range(lens):
            obj = {}
            for key in to_key_name:
                if pd.isnull(target_data[key][i]):
                    obj[key] = None
                else:
                    obj[key] = target_data[key][i]
                pass
            self.sheet_info[sheet_name].append(obj) # 添加原始行数据对象(待拆分)
            pass

        for maps in self.maps_rule:
            if maps['handle'] == 'split':
                to = maps['to']
                for index, obj in enumerate(self.sheet_info[sheet_name]):
                    check_split = obj[to]
                    if pd.isnull(check_split):
                        continue
                    if '\n' in check_split:
                        words = check_split.split('\n')
                        word_set = set(words) # 确保唯一性
                        for word in word_set:
                            if sheet_name in word: # 以sheet页名字为判断标准
                                obj[to] = word  # 将包含sheet页名字的直接原位替换

                            else:
                                obj1 = copy.copy(obj)
                                obj1[to] = word
                                self.sheet_info[sheet_name].append(obj1)    # 将复制得到的行数据对象添加至列表尾部
                            pass
                    pass
            pass

        print("拆分指定行后, 现在的数据行数: ", len(self.sheet_info[sheet_name]))

        for maps in self.maps_rule:
            if maps['handle'] == 'split by':
                to = maps['to']
                split_by = maps['split_by']

                for index, obj in enumerate(self.sheet_info[sheet_name]):
                    check_split_by = obj[split_by]
                    check_split = obj[to]
                    if pd.isnull(check_split):
                        continue

                    if '\n' in check_split:
                        words = check_split.split('\n')
                        word_set = set(words) # 确保唯一性

                        for word in word_set:
                            # Note: 此处以分割后的字符串作为主串
                            if check_split_by in word:
                                # 使用该word来替换
                                obj[to] = word
                                break
                            else:
                                obj[to] = None
                                pass
                            pass
                    pass
            pass
        pass

    def resort_sheet_data(self):
        need_refind = []
        for sheet_name, objs in self.sheet_info.items():
            print("sheet: ", sheet_name, "origin: ", self.origin_lens[sheet_name])
            need_refind += objs[self.origin_lens[sheet_name]:]  # 加入待重新分配的列表
            self.sheet_info[sheet_name] = objs[0:self.origin_lens[sheet_name]]

            print("移除不符合的元素后: ", len(self.sheet_info[sheet_name]))
            pass

        to = ''
        for maps in self.maps_rule:
            if maps['handle'] == 'split':
                to = maps['to']
                pass
            pass

        # print('待重新分配的项: ', need_refind)

        sheet_names = self.sheet_info.keys()
        print(sheet_names)

        for need_refind_obj in need_refind:
            is_find = False

            # 检查待重新分配的行对象的['to']与sheet页名的匹配关系
            for sheet_name in sheet_names:
                if sheet_name in need_refind_obj[to]:
                    # 重新分配
                    self.sheet_info[sheet_name].append(need_refind_obj)
                    is_find = True
                    break
                pass

            if is_find is not True:
                print("需要手动调整的项: ", need_refind_obj)
                pass
            pass

        for sheet_name in sheet_names:
            print("重新整理元素后: ", len(self.sheet_info[sheet_name]))

        pass
